Microsoft Dynamics GP - SQL View for GL Account Review
Hello everyone!
It’s been far too long since my last blog post. I have lots of updates but I wanted to just get to the good stuff first.
I will be sharing a tip at the 2022 Community Summit and I wanted to give you all access to a SQL View that I found and improved. I had a customer with thousands and thousands of accounts that were setup when they first setup GP. Their partner took every possible combo of each segment value and created accounts. This created Balance Sheet accounts for each subdivision/site. Many accounts were never used. They had no easy way to find them! I found a SQL View on the Microsoft Communities site. I added some additional fields and it was perfect!
I have a contest and a survey to help me figure out what I should focus on next. Here’s a link to the survey: Training Dynamo Survey
To learn how to load SQL Views and use them with SmartList Designer or SmartList Builder, visit and subscribe to my YouTube Channel: youtube.com/c/trainingdynamo
--The SQL Script below started here https://community.dynamics.com/gp/f/microsoft-dynamics-gp-forum/144447/gl-account-numbers--cleanup-of-accounts-not-used
--Amber Bell from Training Dynamo added the "Created Date" and "Account Type" and turned into a SQL View
CREATE VIEW _GLAccountLastUsed
AS
SELECT B.ACTNUMST 'Account Number' ,
A.ACTDESCR 'Account Description' ,
A.CREATDDT 'Created Date',
A.ACTINDX 'Account Index',
A.ACCTTYPE 'Account Type',
A.ACTIVE 'Active',
CASE WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT THEN 'Yes'
ELSE ''
END AS NeverUsed ,
ISNULL(D.TRX_Date, 0) AS 'Last Used' ,
DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Years)' ,
DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Months)' ,
DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Days)'
FROM dbo.GL00100 AS A
LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX
LEFT OUTER JOIN ( SELECT ACTINDX ,
MAX(TRX_Date) TRX_Date
FROM ( SELECT ACTINDX ,
MAX(TRXDATE) AS TRX_Date
FROM dbo.GL20000
GROUP BY ACTINDX
UNION ALL
SELECT ACTINDX ,
MAX(TRXDATE) AS TRX_Date
FROM dbo.GL30000
GROUP BY ACTINDX
) AS C
GROUP BY C.ACTINDX
) AS D ON B.ACTINDX = D.ACTINDX
GO
GRANT SELECT ON _GLAccountLastUsed TO DYNGRP